package ita.class1.group2.supermarket.customer.dao;

import ita.class1.group2.supermarket.common.annotation.Autowired;
import ita.class1.group2.supermarket.common.annotation.Component;
import ita.class1.group2.supermarket.common.util.ConnectionManager;
import ita.class1.group2.supermarket.customer.pojo.StoreInformation;
import ita.class1.group2.supermarket.common.dao.impl.BaseDaoImpl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Component
public class StoreInformationDao extends BaseDaoImpl<StoreInformation, String> {

    @Autowired
    private ConnectionManager connectionManager;

    public List<StoreInformation> findStoreInformationsBySids(List<String> sids, Integer pageIndex, Integer pageCount) {
        List<StoreInformation> storeInformations = new ArrayList<>();
        StringBuffer buffer = new StringBuffer();
        for (int i = 0; i<sids.size(); i++) {
            buffer.append("?,");
        }
        String sidsStr = buffer.toString();
        if(sidsStr.length()>0){
            sidsStr = sidsStr.substring(0,sidsStr.length()-1);
        }
        //String sql = "select * from mc_storeinformation where sid in ("+sidsStr+")";
        Integer nttotal = pageCount * pageIndex;
        Integer gttotal = pageCount * (pageIndex - 1);
        String sql = "select * from (select e.*, rownum rn from (select * from mc_storeinformation where sid in ("+sidsStr+") )e where rownum <="+nttotal+") where rn >"+gttotal ;
        System.out.println("sql:"+sql);
        try {
            PreparedStatement statement = connectionManager.getConnection().prepareStatement(sql);
            for (int i = 0; i<sids.size(); i++) {
                statement.setString(i+1,sids.get(i));
            }
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                StoreInformation storeInformation = new StoreInformation(rs.getString("id"),rs.getString("sid"),rs.getString("open"),
                        rs.getString("close"),rs.getDouble("delivery_area"),rs.getString("description"),rs.getString("images"));
                storeInformations.add(storeInformation);
            }
            System.out.println("size:"+storeInformations.size());
            rs.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return storeInformations;
    }

    public int findStoreInformationsCountBySids(List<String> sids) {
        int count = 0;
        StringBuffer buffer = new StringBuffer();
        for (int i = 0; i<sids.size(); i++) {
            buffer.append("?,");
        }
        String sidsStr = buffer.toString();
        if(sidsStr.length()>0){
            sidsStr = sidsStr.substring(0,sidsStr.length()-1);
        }
        String sql = "select count(*) from mc_storeinformation where sid in ("+sidsStr+")";
        try {
            PreparedStatement statement = connectionManager.getConnection().prepareStatement(sql);
            for (int i = 0; i<sids.size(); i++) {
                statement.setString(i+1,sids.get(i));
            }
            ResultSet rs = statement.executeQuery();
            rs.next();
            count = rs.getInt(1);
            System.out.println("count:"+count);
            rs.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }


    public StoreInformation findStoreInformation(String sId) {
        StoreInformation storeInformation = new StoreInformation();
        String sql = "select * from mc_storeinformation where sid = ?" ;
        try {
            PreparedStatement statement = connectionManager.getConnection().prepareStatement(sql);
            statement.setString(1,sId);
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                 storeInformation = new StoreInformation(rs.getString("id"),rs.getString("sid"),rs.getString("open"),
                        rs.getString("close"),rs.getDouble("delivery_area"),rs.getString("description"),rs.getString("images"));
            }
            rs.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return storeInformation;
    }



}
